package me.zhengjie.modules.website.repository;

import me.zhengjie.modules.website.domain.AccessLogs;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author system
 * @since 2024-12-02 16:18:43
 */
@Repository
public interface AccessLogsRepository extends JpaRepository<AccessLogs, Long>, JpaSpecificationExecutor<AccessLogs> {

    // 统计访问的总次数
    @Query("SELECT COUNT(a) FROM AccessLogs a ")
    long countByPage();

    // 统计访问的不同 IP 数量
    @Query("SELECT COUNT(DISTINCT a.ipAddress) FROM AccessLogs a ")
    long countDistinctIpByPage();


    // 按月份统计访问量 (PV)
    @Query("SELECT MONTH(a.visitTime) AS month, COUNT(a) AS count " +
            "FROM AccessLogs a " +
            "WHERE YEAR(a.visitTime) = :year " +
            "GROUP BY MONTH(a.visitTime) " +
            "ORDER BY month")
    List<Object[]> countPageViewsByMonth(@Param("year") int year);


    // 按月份统计独立访问人数 (UV)
    @Query("SELECT MONTH(a.visitTime) AS month, COUNT(DISTINCT a.ipAddress) AS count " +
            "FROM AccessLogs a " +
            "WHERE YEAR(a.visitTime) = :year " +
            "GROUP BY MONTH(a.visitTime) " +
            "ORDER BY month")
    List<Object[]> countUniqueVisitorsByMonth(@Param("year") int year);


    // 按照地区统计年份的用户量和访问量
    @Query("SELECT " +
            "SUBSTRING(a.location, LOCATE('中国', a.location) + 2, 2) AS province, " +
            "COUNT(a) AS visitCount, " +
            "COUNT(DISTINCT a.ipAddress) AS uniqueUsers " +
            "FROM AccessLogs a " +
            "WHERE a.location LIKE '中国%' " +
            "AND YEAR(a.visitTime) = :year " +
            "AND LENGTH(SUBSTRING(a.location, LOCATE('中国', a.location) + 2)) > 0 " + // 新增条件，确保中国后有字符
            "GROUP BY SUBSTRING(a.location, LOCATE('中国', a.location) + 2, 2) " +
            "ORDER BY visitCount DESC")
    List<Object[]> getVisitStatsByProvinceForYear(@Param("year") int year);


}
